<?php 
namespace Admin\Controller;
use PHPExcel_IOFactory;
use PHPExcel;
/**
 * 后台首页控制器
 * @author 麦当苗儿 <zuojiazi@vip.qq.com>
 */
class ExcelController extends AdminController {

 public function out(){
		ini_set('memory_limit','2072M'); 
        $ywstate = D('Ywlb')->where(array('status'=>array('eq',1)))->field('uid,name')->select();
        $kf = D('Member')->field('uid,nickname')->select();
         $map=array();
		
        //查询数据接收包
		$post_data  = I('Reserve');
         //查询数据接收包
		if ($_POST['Reserve']['id']) {
            $map['id'] = array('eq',$_POST['Reserve']['id']);
        }
        if ($_POST['Reserve']['ddbh']) {
            $map['ddbh'] = array('eq',$_POST['Reserve']['ddbh']);
        }
        if ($_POST['Reserve']['name']) {
            $map['name'] = array('LIKE','%'.$_POST['Reserve']['name'].'%');
        }
		if ($_POST['Reserve']['qq']) {
            //$map['qq'] = array('eq',$_POST['Reserve']['qq']);
			$map['qq'] = array('LIKE','%'.$_POST['Reserve']['qq'].'%');
        }
        if ($_POST['Reserve']['phone']) {
            //$map['phone'] = array('eq',$_POST['Reserve']['phone']);
			$map['phone'] = array('LIKE','%'.$_POST['Reserve']['phone'].'%');
        }
		if ($_POST['Reserve']['waqianren']) {
			$map['waqianren'] = array('LIKE','%'.$_POST['Reserve']['waqianren'].'%');
        }
        if ($_POST['Reserve']['jdtime_start'] && $_POST['Reserve']['jdtime_end']) {
            $map['jdtime'] = array('between',array(strtotime($_POST['Reserve']['jdtime_start']),strtotime($_POST['Reserve']['jdtime_end'])));
        }
        if ($_POST['Reserve']['yytime_start'] && $_POST['Reserve']['yytime_end']) {
            $map['yytime'] = array('between',array(strtotime($_POST['Reserve']['yytime_start'].'00:00:00'),strtotime($_POST['Reserve']['yytime_end'].'23:59:59')));
        }
		if ($_POST['Reserve']['jindtime_start'] && $_POST['Reserve']['jindtime_end']) {
            $map['jindtime'] = array('between',array(strtotime($_POST['Reserve']['jindtime_start']),strtotime($_POST['Reserve']['jindtime_end'].'23:59:59')));
        }
		if ($_POST['Reserve']['wddtime_start'] && $_POST['Reserve']['wddtime_end']) {
            $map['wddtime'] = array('between',array(strtotime($_POST['Reserve']['wddtime_start'].'00:00:00'),strtotime($_POST['Reserve']['wddtime_end'].'23:59:59')));
        }
		if ($_POST['Reserve']['lstime_start'] && $_POST['Reserve']['lstime_end']) {
            $map['lstime'] = array('between',array(strtotime($_POST['Reserve']['lstime_start'].'00:00:00'),strtotime($_POST['Reserve']['lstime_end'].'23:59:59')));
        }
   
        if ($_POST['Reserve']['ddtime_start'] && $_POST['Reserve']['ddtime_end']) {
            $map['ddtime'] = array('between',array(strtotime($_POST['Reserve']['ddtime_start'].'00:00:00'),strtotime($_POST['Reserve']['ddtime_end'].'23:59:59')));
        }
        if ($_POST['Reserve']['kf']) {
            $map['kf'] = array('eq',$_POST['Reserve']['kf']);
        }
        if ($_POST['Reserve']['xsjd']) {
            $map['xsjd'] = array('eq',$_POST['Reserve']['xsjd']);
        }
        if ($_POST['Reserve']['xxjd']) {
            $map['xxjd'] = array('eq',$_POST['Reserve']['xxjd']);
        }
        if ($_POST['Reserve']['qd']) {
            $map['qd'] = array('eq',$_POST['Reserve']['qd']);
        }
        if ($_POST['Reserve']['ywstate']) {
            $map['ywstate'] = array('eq',$_POST['Reserve']['ywstate']);
        }
        if ($_POST['Reserve']['lb']) {
            $map['lb'] = array('eq',$_POST['Reserve']['lb']);
        }
		if ($_POST['Reserve']['gkbq']) {
            $map['gkbq'] = array('eq',$_POST['Reserve']['gkbq']);
        }
        $map['status'] = array('eq',1);
		
        $data_yw = D('Yewu')->where($map)->field('id,name
		,phone,wangwang,weibo,qq,jdtime,yytime,jindtime,
		lstime,wddtime,ddtime,bz,xxbz,lb,kf,qd,qdly,xsjd,
		xxjd,setmoney,money,zfkx,zffs,pdtimee,pdtimes,ywstate,
		tj,gkbq,waqianren,xxfwtime')->select();
		
		//var_dump($data_yw);
        foreach ($data_yw as $key => $value) {
            foreach ($ywstate as $key => $v) {
                if ($value['ywstate'] == $v['uid']) {
                   $value['ywstate'] = $v['name'];
                }
                if ($value['qd'] == $v['uid']) {
                   $value['qd'] = $v['name'];
                }
                if ($value['lb'] == $v['uid']) {
                   $value['lb'] = $v['name'];
                }
                if ($value['zfkx'] == $v['uid']) {
                   $value['zfkx'] = $v['name'];
                }
                if ($value['zffs'] == $v['uid']) {
                   $value['zffs'] = $v['name'];
                }

            }
            foreach ($kf as $key => $s) {
                if ($value['kf'] == $s['uid']) {
                   $value['kf'] = $s['nickname'];
                }
                if ($value['xsjd'] == $s['uid']) {
                   $value['xsjd'] = $s['nickname'];
                }
                if ($value['xxjd'] == $s['uid']) {
                   $value['xxjd'] = $s['nickname'];
                }
            }
            if($value['jdtime']){
                $value['jdtime']     =  date('Y-m-d H:i:s',$value['jdtime']);
            }
            if($value['yytime']){
                $value['yytime']     =  date('Y-m-d H:i:s',$value['yytime']); 
            }else{
                $value['yytime']     =  NULL;
            }
            if($value['lstime']){
                $value['lstime']     =  date('Y-m-d H:i:s',$value['lstime']);
            }
			if($value['jindtime']){
                $value['jindtime']     =  date('Y-m-d H:i:s',$value['jindtime']);
            }
            if($value['wddtime']){
                $value['wddtime']    =  date('Y-m-d H:i:s',$value['wddtime']);
            }
            if($value['ddtime']){
                $value['ddtime']     =  date('Y-m-d H:i:s',$value['ddtime']);
            }
            if($value['cztime']){
                $value['cztime']     =  date('Y-m-d H:i:s',$value['cztime']);
            }
            if($value['pdtimes']){
                $value['pdtimes']     =  date('Y-m-d',$value['pdtimes']);
            }else{
                $value['pdtimes']     =  NULL;
            }
			if($value['pdtimee']){
                $value['pdtimee']     =  date('Y-m-d',$value['pdtimee']);
            }else{
                $value['pdtimee']     =  NULL;
            }
            if($value['sdtime']){
                $value['sdtime']    =  date('Y-m-d H:i:s',$value['sdtime']);
            }else{
                $value['sdtime']     =  NULL;
            }
            unset($value['push']); 
            $data[] = $value;
        }
		
		//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
		//import("Org.Util.PHPExcel");
		//import("Org.Util.PHPExcel.Writer.Excel5");
		//import("Org.Util.PHPExcel.IOFactory.php");	
        $filename="业务数据";
        $headArr=array("ID","姓名","电话","旺旺","微博","微信","建单时间","预约时间",
		"进店时间","流失时间","未到店时间","成交时间","线上备注","线下备注","类别","客服"
		,"渠道","来源信息","线上门市","线下门市","套系价格","实收金额","支付款项","支付方式"
		,"排挡时间开始","排挡时间结束","业务状态","推荐","顾客标签","挖潜人","线下服务时间");
        $this->getExcel($filename,$headArr,$data);
        $this->getExcel($filename,$headArr,$data);
    }

    private function getExcel($fileName,$headArr,$data){
		
			
            //对数据进行检验
            if(empty($data) || !is_array($data)){
                die("data must be a array");
            }
            //检查文件名
            if(empty($fileName)){
                exit;
            }

            $date = date("Y_m_d",time());
            $fileName .= "_{$date}.xls";

            //创建PHPExcel对象，注意，不能少了\
			vendor("PHPExcel.PHPExcel");
		//	$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = new PHPExcel();
		
            $objProps = $objPHPExcel->getProperties();
            
            //设置表头
            $key = ord("A");
			$key2 = ord("@");
            foreach($headArr as $v){
                //$colum = chr($key);
					if($key>ord("Z")){
						$key2 += 1;
						$key = ord("A");
						$colum = chr($key2).chr($key);//超过26个字母时才会启用  
					}else{
						if($key2>=ord("A")){
							$colum = chr($key2).chr($key);//超过26个字母时才会启用  
						}else{
							$colum = chr($key);
						}
					}
		
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
                $key += 1;
            }
            
            $column = 2;
            $objActSheet = $objPHPExcel->getActiveSheet();
            foreach($data as $key => $rows){ //行写入
                $span = ord("A");
				$span2 = ord("@");
                foreach($rows as $keyName=>$value){// 列写入
                    //$j = chr($span);
					if($span>ord("Z")){
						$span2 += 1;
						$span = ord("A");
						$j = chr($span2).chr($span);//超过26个字母时才会启用  
					}else{
						if($span2>=ord("A")){
							$j = chr($span2).chr($span);//超过26个字母时才会启用  
						}else{
							$j = chr($span);
						}
					}
                    $objActSheet->setCellValue($j.$column, $value);
                    $span++;
                }
                $column++;
        }
		
            $fileName = iconv("utf-8", "gb2312", $fileName);
            //重命名表
            // $objPHPExcel->getActiveSheet()->setTitle('test');
            //设置活动单指数到第一个表,所以Excel打开这是第一个表
            $objPHPExcel->setActiveSheetIndex(0);
			ob_end_clean();//清除缓冲区,避免乱码 
			header("Content-Type: application/vnd.ms-excel; charset=utf-8"); 
			header('Content-Disposition: attachment;filename='.$filename); 
			header('Cache-Control: max-age=0'); 
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); 
			$objWriter->save('php://output'); 
			exit; 

        }

}